package com.fzy.core.system.service;

import ge.core.system.entity.Entity;
import ge.core.system.entity.Property;

import java.sql.ResultSet;
import java.sql.SQLException;
import java.util.LinkedList;
import java.util.List;

import javax.naming.NamingException;

import org.springframework.jdbc.core.RowMapper;

import com.fzy.core.entity.Page;
import com.fzy.core.system.entity.PropertyWrapper;
import com.fzy.core.utils.DateUtils;
import com.fzy.core.utils.NumberUtils;
import com.fzy.core.utils.StringUtils;

public class H2DbService extends AbstractDbService {

	public H2DbService(String driverClassName, String url, String username,
			String password) {
		super(driverClassName, url, username, password);
	}

	public H2DbService(String jndiName){
		super(jndiName);
	}

	public H2DbService(){
	}

	public Page getEntitiesFromTables(EntityQuery entityQuery, Page page) {
		String sql = "";
		sql += "SELECT   table_name, remarks comments";
		sql += "  FROM   information_schema.tables";
		sql += " WHERE   table_cataLog = 'TEST' AND table_schema = 'PUBLIC'";
		List params = new LinkedList();
		String tableName = entityQuery.getTableName();
		if(!StringUtils.isBlank(tableName)){
			tableName = tableName.toUpperCase();
			sql += getConditionAntLike(tableName, "table_name", params);
		}
		sql += getConditionSql(entityQuery.getComments(), "remarks =", params);
		String orders = "";
		orders += getOrderSql("tableName", "table_name", page);
		sql += orders;
		return queryPage(sql, params.toArray(), new RowMapper() {
			public Object mapRow(ResultSet rs, int rowNum) throws SQLException {
				Entity e = new Entity();
				e.setHasPrefix("0");
				e.setIsWork("1");
				e.setIsParentMapping("1");
				e.setPackageText("ge.app");

				String tableName = rs.getString("table_name").toLowerCase();
				String comment = escapeComment(rs.getString("comments"));
				e.setTableName(tableName);
				e.setCommentText(comment);
				handleCommentOfEntity(e, comment);

				e.setNameShow(getNameShow(tableName, comment));
				e.setNameText(tableName);

				return e;
			}
		}, page);
	}

	public Entity getEntityByTableName(String tableName) {
		if (StringUtils.isBlank(tableName))
			return new Entity();
		tableName = tableName.toUpperCase();
		Entity result = null;
		String sql = "";
		sql += "SELECT   table_name, remarks comments";
		sql += "  FROM   information_schema.tables";
		sql += " WHERE   table_cataLog = 'TEST' AND table_schema = 'PUBLIC'";
		List params = new LinkedList();
		sql += getConditionSql(tableName, "table_name =", params);
		List l = query(sql, params.toArray(), new RowMapper() {
			public Object mapRow(ResultSet rs, int rowNum) throws SQLException {
				Entity e = new Entity();
				e.setHasPrefix("0");
				e.setIsWork("1");
				e.setIsParentMapping("1");
				e.setPackageText("ge.app");

				String tableName = rs.getString("table_name").toLowerCase();
				String comment = escapeComment(rs.getString("comments"));
				e.setTableName(tableName);
				e.setCommentText(comment);
				handleCommentOfEntity(e, comment);

				e.setNameShow(getNameShow(tableName, comment));
				e.setNameText(tableName);

				return e;
			}
		});
		if (l.size() == 1)
			result = (Entity) l.get(0);
		return result;
	}

	public List getPropertiesByTableName(String tableName) {
		if (StringUtils.isBlank(tableName))
			return new LinkedList();
		tableName = tableName.toUpperCase();
		String sql = "";
		sql += "SELECT   a.table_name,";
		sql += "         a.column_name,";
		sql += "         a.remarks comments,";
		sql += "         type_name data_type,";
		sql += "         character_octet_length data_length,";
		sql += "         numeric_precision data_precision,";
		sql += "         numeric_scale data_scale,";
		sql += "         nullable,";
		sql += "         b.primary_key constraint_type";
		sql += "  FROM      information_schema.columns a";
		sql += "         LEFT OUTER JOIN";
		sql += "            INFORMATION_SCHEMA.INDEXES b";
		sql += "         ON     a.table_catalog = b.table_catalog";
		sql += "            AND a.table_schema = b.table_schema";
		sql += "            AND a.table_name = b.table_name";
		sql += "            AND a.column_name = b.column_name";
		sql += " WHERE   a.table_cataLog = 'TEST' AND a.table_schema = 'PUBLIC'";
		sql += " 		 AND a.table_name = ?";
		List params = new LinkedList();
		params.add(tableName);
		return query(sql, params.toArray(), new RowMapper() {
			public Object mapRow(ResultSet rs, int rowNum) throws SQLException {
				Property p = new Property();
				p.setIsQuery("0");
				p.setIsShow("1");
				p.setIsWork("1");

				int scale = NumberUtils.toInt(rs.getString("data_scale"), PropertyWrapper.SCALE_DEFAULT);
				String type = dbType2propertyType(
						rs.getString("data_type"), scale);
				String columnName = rs.getString("column_name").toLowerCase();
				String comment = escapeComment(rs.getString("comments"));
				String constraintType = rs.getString("constraint_type");
				boolean isKey = "TRUE".equals(constraintType);
//				boolean isRef = false;
				boolean isUnique = isKey;
				boolean isNotNull = "0".equals(rs.getString("nullable"));
				p.setColumnName(columnName);
				p.setCommentText(comment);
				handleCommentOfProperty(p, comment);
				p.setIsKey(isKey ? "1" : "0");
				p.setIsUnique(isUnique ? "1" : "0");
				p.setIsNotNull(isNotNull ? "1" : "0");
				p.setNameShow(getNameShow(columnName, comment));
				p.setNameText(StringUtils.underline2camel(columnName));
				p.setPropertyType(type);
//				if (isRef) {
//					p.setRefTable(rs.getString("r_table_name").toLowerCase());
//					p.setRefColumn(rs.getString("r_column_name").toLowerCase());
//				}
				if (PropertyWrapper.STRING.equals(type)) {
					p.setLengthValue(NumberUtils.createLong(rs
							.getString("data_length")));
				} else if (PropertyWrapper.NUMBER.equals(type)) {
					p.setPrecisionValue(NumberUtils.createLong(rs
							.getString("data_precision")));
					p.setScaleValue(NumberUtils.createLong(rs
							.getString("data_scale")));
				} else if (PropertyWrapper.DATE.equals(type)){
//					p.setDateFormatShow(DateUtils.PATTERN_SHOW);
				}
				return p;
			}
		});
	}
	
	private String dbType2propertyType(String dbType, int scale) {
		String result = "";
		if ("VARCHAR".equalsIgnoreCase(dbType)) {
			result = PropertyWrapper.STRING;
		}
		if ("BIGINT".equalsIgnoreCase(dbType)) {
			result = PropertyWrapper.LONG;
		}
		if ("DECIMAL".equalsIgnoreCase(dbType)) {
				result = PropertyWrapper.NUMBER;
				if(scale == 0)
					result = PropertyWrapper.LONG;
		}
		if ("TIMESTAMP".equalsIgnoreCase(dbType)) {
			result = PropertyWrapper.DATE;
		}
		return result;
	}
}
